package com.xiaoye;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;

public class InsertStudents {
    public static void main(String[] args) {
        String fileName = "E:\\Project\\DigitalTwinPic\\StudentList.xls"; // 学生信息文件路径
        String url = "jdbc:mysql://47.93.162.187:3306/digitaltwin"; // 数据库连接URL
        String username = "root"; // 数据库用户名
        String password1 = "ZXCzxc1@"; // 数据库密码

        try (Connection conn = DriverManager.getConnection(url, username, password1)) {
            // 读取学生信息文件
            BufferedReader reader = new BufferedReader(new FileReader(fileName));
            String line;

            // 读取Excel文件
            FileInputStream file = new FileInputStream(fileName);
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            Sheet sheet = workbook.getSheetAt(0);

            // 遍历每一行并插入数据库
            Iterator<Row> rowIterator = sheet.iterator();
             Integer id = 200;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                String name = row.getCell(0).getStringCellValue();
                String stu_no = row.getCell(1).getStringCellValue();
                String class_no = row.getCell(2).getStringCellValue();
                String password = "e10adc3949ba59abbe56e057f20f883e";
                String avatar = null;
                int status = 0;
                String sex = "男";
                String baopoFlag = "False";
                String neiyaFlag = "False";
                String waiyaFlag = "False";

                // 插入数据到数据库
                String sql = "INSERT INTO student (id, name, stu_no,class_no, password,avatar,status,sex,baopoFlag,neiyaFlag,waiyaFlag) VALUES (?,?, ?, ?,?, ?,?, ?,?, ?,?)";
                PreparedStatement statement = conn.prepareStatement(sql);
                statement.setInt(1,id);
                statement.setString(2, name);
                statement.setString(3, stu_no);
                statement.setString(4, class_no);
                statement.setString(5, password);
                statement.setString(6, avatar);
                statement.setInt(7,status);
                statement.setString(8, sex);
                statement.setString(9, baopoFlag);
                statement.setString(10, neiyaFlag);
                statement.setString(11, waiyaFlag);
                statement.executeUpdate();
                id++;
            }

            // 关闭资源
            workbook.close();
            conn.close();
            file.close();
            System.out.println("学生信息插入成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
